#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


-- 插入数据到 edu_online_ads.ads_attendance_count 正常出勤统计表
insert into hive.edu_online_ads.ads_attendance_count
select
    class_date as dt,
    class_id,
    morning_attendance_count,
    afternoon_attendance_count,
    evening_attendance_count,
    cast((cast(morning_attendance_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as morning_attendance_ratio,
    cast((cast(afternoon_attendance_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as afternoon_attendance_ratio,
    cast((cast(evening_attendance_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as evening_attendance_ratio
from hive.edu_online_dws.dws_attendance;
-- 插入数据到 edu_online_ads.ads_late_count 迟到统计表
insert into hive.edu_online_ads.ads_late_count
select
    class_date as dt,
    class_id,
    morning_late_count,
    afternoon_late_count,
    evening_late_count,
    cast((cast(morning_late_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as morning_late_ratio,
    cast((cast(afternoon_late_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as afternoon_late_ratio,
    cast((cast(evening_late_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as evening_late_ratio
from hive.edu_online_dws.dws_attendance;
-- 插入数据到 edu_online_ads.ads_leave_count 请假统计表
insert into hive.edu_online_ads.ads_leave_count
select
    class_date as dt,
    class_id,
    morning_leave_count,
    afternoon_leave_count,
    evening_leave_count,
    cast((cast(morning_leave_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as morning_leave_ratio,
    cast((cast(afternoon_leave_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as afternoon_leave_ratio,
    cast((cast(evening_leave_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as evening_leave_ratio
from hive.edu_online_dws.dws_attendance;
-- 插入数据到 edu_online_ads.ads_absent_count 旷课统计表
insert into hive.edu_online_ads.ads_absent_count
select
    class_date as dt,
    class_id,
    morning_absent_count,
    afternoon_absent_count,
    evening_absent_count,
    cast((cast(morning_absent_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as morning_absent_ratio,
    cast((cast(afternoon_absent_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as afternoon_absent_ratio,
    cast((cast(evening_absent_count as decimal(38,4)) / studying_student_count * 100)as decimal(5,2)) as evening_absent_ratio
from hive.edu_online_dws.dws_attendance;"
